﻿using System;
using System.Xml;
using System.Data;
using System.Data.SqlClient;
using ESpace.DalTools.Common;
using MySql.Data.MySqlClient;

namespace ESpace.DalTools.MySql
{
	public class MySqlBuilderBase : BuilderBase
	{
		#region 属性定义
		private string _DBName;
		public string DBName
		{
			get
			{
				return _DBName;
			}
			set
			{
				_DBName = value;
			}
		}

		private string _DBServer;
		public string DBServer
		{
			get
			{
				return _DBServer;
			}
			set
			{
				_DBServer = value;
			}
		}

		private string _DBUid;
		public string DBUid
		{
			get
			{
				return _DBUid;
			}
			set
			{
				_DBUid = value;
			}
		}

		private string _DBPassword;
		public string DBPassword
		{
			get
			{
				return _DBPassword;
			}
			set
			{
				_DBPassword = value;
			}
		}

		protected MySqlConnection _conn;
		private XmlDocument domSql2DotNet;
		#endregion

		// 构造函数
		public MySqlBuilderBase()
		{
			// 读取配置文件
			string sSplit = System.AppDomain.CurrentDomain.BaseDirectory.Substring(System.AppDomain.CurrentDomain.BaseDirectory.Length - 1);
			string ConfigPath;
			if (sSplit != @"\" && sSplit != "/")
				ConfigPath = System.AppDomain.CurrentDomain.BaseDirectory + @"\";
			else
				ConfigPath = System.AppDomain.CurrentDomain.BaseDirectory;
			string sConfigFileName = ConfigPath + @"Reflection\SQL2DotNet.xml";
			domSql2DotNet = new XmlDocument();
			domSql2DotNet.Load(sConfigFileName);
		}

		/// <summary>
		/// 根据sql类型获取DotNet语言类型
		/// </summary>
		/// <param name="sSqlType">sql类型</param>
		/// <returns>DotNet语言类型</returns>
		protected override string GetLanguageTypeName(string sSqlType)
		{
			XmlNode xmlNode = domSql2DotNet.SelectSingleNode("DataBase/" + sSqlType);

			//获取节点的属性，并循环取出需要的属性值
			XmlAttributeCollection xmlAttr = xmlNode.Attributes;
			string strReturn = "";
			for (int i = 0; i < xmlAttr.Count; i++)
			{
				if (xmlAttr.Item(i).Name == "langType")
				{
					strReturn = xmlAttr.Item(i).Value;
					break;
				}
			}
			return strReturn;
		}

		/// <summary>
		/// 把SqlServer中的类型名称转换成.Net语言中SqlDBType的类型
		/// </summary>
		/// <param name="sSqlType">Sql类型</param>
		/// <returns>.Net中SqlType类型</returns>
		protected override string GetDbTypeName(string sSqlType)
		{
			XmlNode xmlNode = domSql2DotNet.SelectSingleNode("DataBase/" + sSqlType);

			//获取节点的属性，并循环取出需要的属性值
			XmlAttributeCollection xmlAttr = xmlNode.Attributes;
			string strReturn = "";
			for (int i = 0; i < xmlAttr.Count; i++)
			{
				if (xmlAttr.Item(i).Name == "dbType")
				{
					strReturn = xmlAttr.Item(i).Value;
					break;
				}
			}
			return strReturn;
		}

		/// <summary>
		/// 判断Sql类型是否允许定义长度
		/// </summary>
		/// <param name="sSqlType">Sql类型</param>
		/// <returns>
		/// true，允许。false，不允许
		/// </returns>
		protected override bool IfTypeAllowLength(string sSqlType)
		{
			XmlNode xmlNode = domSql2DotNet.SelectSingleNode("DataBase/" + sSqlType);

			//获取节点的属性，并循环取出需要的属性值
			XmlAttributeCollection xmlAttr = xmlNode.Attributes;
			string strReturn = "";
			for (int i = 0; i < xmlAttr.Count; i++)
			{
				if (xmlAttr.Item(i).Name == "allowLengthDef")
				{
					strReturn = xmlAttr.Item(i).Value;
					break;
				}
			}
			return strReturn == "1";
		}

		/// <summary>
		/// 连接到数据库
		/// </summary>
		public override void ConnectToDB()
		{
			string sConnString = ConnectionString;
			_conn = new MySqlConnection(sConnString);
			_conn.Open();
		}

		/// <summary>
		/// 断开数据库连接
		/// </summary>
		public override void DisconnectFromDB()
		{
			if (_conn.State == ConnectionState.Open)
			{
				_conn.Close();
			}
		}

		/// <summary>
		/// 根据SqlServer的数据类型的编号，取得数据类型名称。
		/// 如1对应于char，2对应于numeric
		/// </summary>
		/// <param name="sSqlDataTypeID">数据类型编号</param>
		/// <returns>数据类型的名称</returns>
		protected string GetSqlDataType(short iSqlDataTypeID)
		{
			//SqlCommand sqlComm = new SqlCommand("sp_datatype_info", _conn);
			//sqlComm.CommandType = CommandType.StoredProcedure;
			//SqlParameter sqlParam = new SqlParameter("@data_type", SqlDbType.Int);
			//sqlParam.Value = iSqlDataTypeID;
			//sqlComm.Parameters.Add(sqlParam);
			//return (string)sqlComm.ExecuteScalar();
			return "";
		}

		/// <summary>
		/// 获取数据库中所有表
		/// </summary>
		/// <returns>数据库中包含的表</returns>
		public override DataTable GetAllTables()
		{
			DataTable dtTables = new DataTable();
			MySqlDataAdapter da = new MySqlDataAdapter();


			da.SelectCommand = new MySqlCommand("show tables",_conn);
			da.Fill(dtTables);

			return dtTables;

		}

		/// <summary>
		/// 获取表的基本信息
		/// </summary>
		/// <param name="TableName"></param>
		/// <returns></returns>
		protected override DataTable GetATable(string TableName)
       {
//            string strSql = @"select
//[TableName]=[Tables].name,[TableOwner]=[Schemas].name
//from sys.tables as [Tables]
//inner join sys.schemas as [Schemas]
//on [Tables].schema_id=[Schemas].schema_id
//where [Tables].name='" + TableName + "'";

//            SqlCommand sqlComm = new SqlCommand(strSql, _conn);
//            sqlComm.CommandType = CommandType.Text;

//            SqlDataAdapter sdaTables = new SqlDataAdapter(sqlComm);
//            DataTable dtTables = new DataTable();
//            sdaTables.Fill(dtTables);

//            return dtTables;
			return null;
		}

		/// <summary>
		/// 获取用户表
		/// </summary>
		/// <returns>数据库中的用户表</returns>
		public override DataTable GetUserTables()
		{
			DataTable dtTables = new DataTable();
			MySqlDataAdapter da = new MySqlDataAdapter();


			da.SelectCommand = new MySqlCommand("show tables", _conn);
			da.Fill(dtTables);

			return dtTables;
		}

		

		/// <summary>
		/// 根据表名获取字段列表
		/// </summary>
		/// <param name="sTableName">表名</param>
		/// <returns>字段列表</returns>
		public override DataTable GetColumns(string sTableName)
		{
			string strSql = @"SHOW FULL COLUMNS FROM " + sTableName;

			// 获取字段
			DataTable dtColumn = new DataTable("Columns");
			MySqlDataAdapter da = new MySqlDataAdapter();
			da.SelectCommand = new MySqlCommand(strSql,_conn);
			da.Fill(dtColumn);

			// 添加扩展列
			dtColumn.Columns.Add("IsPK", Type.GetType("System.Byte"));
			dtColumn.Columns.Add("IsLengthDef", Type.GetType("System.Byte"));
			dtColumn.Columns.Add("LanguageType", Type.GetType("System.String"));
			dtColumn.Columns.Add("DbType", Type.GetType("System.String"));
			dtColumn.Columns.Add("HbmType", Type.GetType("System.String"));
			dtColumn.Columns.Add("Desc", Type.GetType("System.String"));
			// 获取表的主键
			DataTable dtPkey = GetPrimaryKeys(sTableName);

			foreach (DataRow row in dtColumn.Rows)
			{
				// 判断列是否为主键
				if (dtPkey.Select("COLUMN_NAME='" + (string)row["ColumnName"] + "'").Length != 0)
					row["IsPK"] = 1;
				else
					row["IsPK"] = 0;

				// 判断列的长度是否可定义
				string sColumnName = (string)row["ColumnName"];
				string sDataType = (string)row["SystemTypeName"];
				bool bIfAllowLength = IfTypeAllowLength(sDataType);
				row["IsLengthDef"] = bIfAllowLength;
				row["LanguageType"] = GetLanguageTypeName(sDataType);
				row["DbType"] = GetDbTypeName(sDataType);
				row["HbmType"] = GetHbmTypeName(sDataType);

				//if (Convert.IsDBNull(row["Description"]))
				//{
				//    row["Desc"] = "";
				//}
				//else
				//{
				//    row["Desc"] = (string)row["Description"];

				//}
				row["Desc"] = "";

				if ((string)row["DbType"] == "SqlDbType.VarChar" || (string)row["DbType"] == "SqlDbType.VarBinary")
				{
					if ((short)row["MaxLength"] <= 0)
						row["MaxLength"] = 8000;
				}
				if ((string)row["DbType"] == "SqlDbType.NVarChar")
				{
					if ((short)row["MaxLength"] <= 0)
						row["MaxLength"] = 4000;
					else
						row["MaxLength"] = ((short)row["MaxLength"]) / 2;
				}
			}
			//dtColumn.Columns.Remove("Description");

			return dtColumn;
		}

		/// <summary>
		/// 获取指定表的主键
		/// </summary>
		/// <param name="sTableName">表名</param>
		/// <returns>主键列表</returns>
		protected override DataTable GetPrimaryKeys(string sTableName)
		{
			//show index from UserConfigV2 where Key_name='PRIMARY'

			DataTable dtPkey = new DataTable("PrimaryKeys");
			string strSql = "show index from "+sTableName +" where Key_name='PRIMARY'";

			MySqlDataAdapter da = new MySqlDataAdapter();
			da.SelectCommand = new MySqlCommand(strSql, _conn);
			da.Fill(dtPkey);
			return dtPkey;
		}

		/// <summary>
		/// 获取一个表的外键
		/// </summary>
		/// <param name="sTableName">表名</param>
		/// <returns>表的所有外键</returns>
		protected override XmlDocument GetForeignKeys(string sTableName)
		{
			return null;
		}

		/// <summary>
		/// 获取用户自定义存储过程列表
		/// </summary>
		/// <returns></returns>
		public override DataTable GetStoreProcedures()
		{
			return null;
		}

		/// <summary>
		/// 获取用户自定义函数列表
		/// </summary>
		/// <returns></returns>
		protected override DataTable GetUserDefineFunctions()
		{
			return null;
		}

		/// <summary>
		/// 获取函数或存储过程的参数列表
		/// </summary>
		/// <param name="sSpName">函数或存储过程名称</param>
		/// <returns>参数列表</returns>
		protected override DataTable GetStoreProcedureColumns(string sSpName)
		{
			return null;
		}

		protected override DataTable GetAStoreProcedure(string ProdecureName)
		{


			return null;
		}


		private static string GetHbmTypeName(string systype)
		{

			#region 对应关系
			//string HbmType = systype;

			//switch (systype.Trim().ToLower())
			//{
			//    case "bool":
			//        HbmType = "Boolean";
			//        break;
			//    case "byte":
			//        HbmType = "Byte";
			//        break;
			//    case "sbyte":
			//        HbmType = "SByte";
			//        break;
			//    case "char":
			//        HbmType = "Char";
			//        break;
			//    case "decimal":
			//        HbmType = "Decimal";
			//        break;
			//    case "double":
			//        HbmType = "Double";
			//        break;
			//    case "float":
			//        HbmType = "Single";
			//        break;
			//    case "int":
			//        HbmType = "Int32";
			//        break;
			//    case "uint":
			//        HbmType = "UInt32";
			//        break;
			//    case "long":
			//        HbmType = "Int64";
			//        break;
			//    case "ulong":
			//        HbmType = "UInt64";
			//        break;
			//    case "short":
			//        HbmType = "Int16";
			//        break;
			//    case "ushort":
			//        HbmType = "UInt16";
			//        break;
			//    case "string":
			//        HbmType = "String";
			//        break;
			//}
			//return HbmType;
			#endregion

			#region 其他对应关系
			string HbmType = systype;
			switch (systype.Trim().ToLower())
			{

				case "string":
				case "nvarchar":
				case "nchar":
				case "ntext":
					HbmType = "String";
					break;
				case "text":
				case "char":
				case "varchar":
					HbmType = "AnsiString";
					break;
				case "datetime":
				case "smalldatetime":
					HbmType = "DateTime";
					break;
				case "smallint":
					HbmType = "Int16";
					break;
				case "tinyint":
					HbmType = "Byte";
					break;
				case "int":
					HbmType = "Int32";
					break;
				case "bigint":
				case "long":
					HbmType = "Int64";
					break;
				case "float":
					HbmType = "Double";
					break;
				case "real":
				case "numeric":
				case "decimal":
					HbmType = "Decimal";
					break;
				case "money":
				case "smallmoney":
					HbmType = "Currency";
					break;
				case "bool":
				case "bit":
					HbmType = "Boolean";
					break;
				case "binary":
				case "varbinary":
					HbmType = "Binary";
					break;
				case "image":
					HbmType = "Image";
					break;
				case "uniqueidentifier":
					HbmType = "Guid";
					break;
				case "timestamp":
					HbmType = "String";
					break;
				default:
					HbmType = "String";
					break;
			}
			return HbmType;
			#endregion
		}

	}

}
